SQL-Query-question

SQL-Query-question

Post by Joel Ekstran » Thu, 04 Mar 1999 04:00:00



I have an SQL-question.

I have two tables, techdata and model_tech.

The techdata looks like this:
id          name
1           Motor
2           Bromsar fram
3           Bromsar bak

and model_tech like this:

model_id    tech_id     value
50                 1            stora motorn
50                 2            bra frambroms
50                 3           bra bakbroms
51                 1           joelmotor

I would like to get all the data with model_id=51, but also the
techdata.name and techdata.id for the rows that dont have a corresponding
value in the model_tech-table. The data I want is this:

name                   tech_id     value

Motor                   1            joelmotor
Bromsar fram     2            <NULL>
Bromsar bak       3           <NULL>

Does anyone know how to do it?

regards Joel

___________________________________________________________

Joel Ekstrand


web                           http://www.sandberg.se/
phone                       +46 (0)31 - 701 65 38
mobil                         +46 (0)708 - 507598
___________________________________________________________

 
 
 

SQL-Query-question

Post by Christoph Muthman » Thu, 04 Mar 1999 04:00:00


Hi Joel,
what do you think of a UNION with a NOT IN SELECT?

Like This:

select t.name, m.tech_id, m.value
from  techdata t,
          model_tech m
where  m.tech_id = t.id
and m.model_id = 51
UNION
select t.name, t.id, NULL as name
from  techdata t
where t.id not in (select m1.tech_id from model_tech m1 where m1.model_id =
51)

Have a nice day!
Christoph


>I have an SQL-question.

>I have two tables, techdata and model_tech.

>The techdata looks like this:
>id          name
>1           Motor
>2           Bromsar fram
>3           Bromsar bak

>and model_tech like this:

>model_id    tech_id     value
>50                 1            stora motorn
>50                 2            bra frambroms
>50                 3           bra bakbroms
>51                 1           joelmotor

>I would like to get all the data with model_id=51, but also the
>techdata.name and techdata.id for the rows that dont have a corresponding
>value in the model_tech-table. The data I want is this:

>name                   tech_id     value

>Motor                   1            joelmotor
>Bromsar fram     2            <NULL>
>Bromsar bak       3           <NULL>

>Does anyone know how to do it?

>regards Joel

>___________________________________________________________

>Joel Ekstrand


>web                           http://www.sandberg.se/
>phone                       +46 (0)31 - 701 65 38
>mobil                         +46 (0)708 - 507598
>___________________________________________________________


 
 
 

SQL-Query-question

Post by Isaac Bla » Thu, 04 Mar 1999 04:00:00


SELECT td.name, tech_id = td.id, mt.value
FROM techdata AS td LEFT OUTER JOIN model_tech AS mt
        ON td.id = mt.tech_id AND mt.model_id = 51

>I have an SQL-question.

>I have two tables, techdata and model_tech.

>The techdata looks like this:
>id          name
>1           Motor
>2           Bromsar fram
>3           Bromsar bak

>and model_tech like this:

>model_id    tech_id     value
>50                 1            stora motorn
>50                 2            bra frambroms
>50                 3           bra bakbroms
>51                 1           joelmotor

>I would like to get all the data with model_id=51, but also the
>techdata.name and techdata.id for the rows that dont have a corresponding
>value in the model_tech-table. The data I want is this:

>name                   tech_id     value

>Motor                   1            joelmotor
>Bromsar fram     2            <NULL>
>Bromsar bak       3           <NULL>

>Does anyone know how to do it?

>regards Joel

>___________________________________________________________

>Joel Ekstrand


>web                           http://www.sandberg.se/
>phone                       +46 (0)31 - 701 65 38
>mobil                         +46 (0)708 - 507598
>___________________________________________________________

 
 
 

1. SQL Query question and unload db question

Hello,

I've already accomplished part of what I need to do by going three
sides around the barn, but wondered if there was an easier way.

What I wanted was just a list of the table names in a db.

To do that, I ran help tables *.t*, which, of course, got me _all_ the
information about each table, then did a search, cut and paste routine
on all that to get just the table names into a concise list.

Is there some syntax I missed someplace, or a query routine that would
produce just the table names in a db? What would have really been
peachy, is a routine that would have pulled out only those table names
ending in 000, 110, or 700. The table name format is something like
ttccom010110.

Second part is a question about unloaddb. We're moving a 2.0/8125
install to 2.5/whatever install. The tools that come with the front end
app are apparently Baan tools (the app was originally written in that,
several generations ago).

The typical instructions call for running a bdbpre command line routine
against the aforementioned table list, which creates a delimited file
suitable for doing a bdbpost back into the new install tables.

Are there any problems in moving directly from 2.0 to 2.5? And, is
there anything special or or more preferable about using the
unloaddb/loaddb command line arguments?

Many thanks and for those celebrating Memorial Day, hope it was good
for you.

Regards,

Mike Ossing
Rodem, Inc.
Cincinnati, OH
513-922-6140

2. How do I connect to an Oracle database from SQLServer 7.0

3. SQL Query Question...can I do this?

4. rowcount -- can it be done?

5. SQL query question

6. Error with open connection

7. SQL Query Question

8. OH-ORACLE SYSTEMS ANALYST/ADMINISTRATOR

9. A SQL query question...

10. SQL Query Question - Inserting new records?

11. SQL Query Question

12. Transact-SQL Query Question

13. A SQL Query Question