Two same column but different queries

1

Hello, hope this is a simple sql-problem. I'm not an sql-champion but do a C++ project and need urgently quick help and hope do be clear enough to form a good question:

I have a table "companies".

ID | company

1 | Siemens
2 | Boeing
3 | Rosneft

and a table "requests"

ID | fromComp | toComp
1 | 2 | 3
2 | 1 | 3
3 | 2 | 1

Now I need to retrieve the clearnames of the companies in hopefully one request.

I can do a simple JOIN and a SELECT to retrieve one of both names. But how is the syntax for creating a query containing two times the same column but with different search conditions?

company | company
(from) | (to)

Thank you.

regards, Lars

2

hi

I am thinking

select ( (select with first condition) + "|"+ (select with second condition) )

Please check and let me know if this works for you

Thanks

1 Like
3

Thanks for that quick reply. I'm out of office today. I'll check asap and let you know. Thx.

4

Table aliases is the answer:

SELECT c_from.company + ' | ' + c_to.company
FROM requests r
INNER JOIN companies c_from ON c_from.ID = r.fromComp
INNER JOIN companies c_to ON c_to.ID = r.toComp
1 Like
5

Hallo Guys, hallo Scott once again. I tried out your ideas. somehow both do work. ...except: While I'm not really familiar with sql yet I don't know what exactely it is, but I try something like this:

SELECT... // somecolums // ...c1.Institution, c2.Institution
FROM produktion p, requests r, contacts c1, contacts c2
WHERE p.P_ID = r.P_ID // pick some specific productionIDs
AND c1.K_ID = r.fromK_ID
AND c2.K_ID = r.toK_ID

now it almost works. except when the fromK_ID or toKID field is empty, the whole job is not found.

???

any idea?

thanks and regard, Lars

ps: where are the code-tags on this site?

6

Good point. Need to use OUTER joins:

SELECT ISNULL(c_from.company, '') + ' | ' + ISNULL(c_to.company, '') AS from_to_company
FROM requests r
LEFT OUTER JOIN companies c_from ON c_from.ID = r.fromComp
LEFT OUTER JOIN companies c_to ON c_to.ID = r.toComp

7

You can avoid the ISNULL function by using the new CONCAT function...which handles that for you.

SELECT CONCAT(c_from.company, ' | ', c_to.company) AS from_to_company
  FROM requests r
  LEFT OUTER JOIN companies c_from ON c_from.ID = r.fromComp
  LEFT OUTER JOIN companies c_to ON c_to.ID = r.toComp

You really should start utilization the INNER/LEFT/RIGHT JOIN syntax - it makes the queries much easier to read and much easier to determine how the tables are related.

SELECT... // somecolums // ...c1.Institution, c2.Institution
FROM produktion p, requests r, contacts c1, contacts c2
WHERE p.P_ID = r.P_ID // pick some specific productionIDs
AND c1.K_ID = r.fromK_ID
AND c2.K_ID = r.toK_ID

Should be rewritten as:

SELECT ... // somecolums // ...c1.Institution, c2.Institution
  FROM produktion p
 INNER JOIN requests r ON r.P_ID = p.P_ID
  LEFT JOIN contacts c1 ON c1.K_ID = r.fromK_ID
  LEFT JOIN contacts c2 ON c2.K_ID = r.toK_ID

In fact - you cannot utilize the old style outer join syntax anyways as it has been deprecated.

8

Okay. You're all great. and I need a break.

Doing so, the programm I'm on (libreOffice) understands, but rewrites it in Scott's style mixed up with what you call old style. As I wrote in a former statement, I'm working on an old and dirty accessdB on an old and dirty winNT4.0 virtual machine. So,...what I need is a dirty solution. And a drink.
Thanks for guiding me so far. You're great.
Lars

9

Afaik, the only way to specify an OUTER JOIN in the WHERE is to use * = or = *:

SELECT... // somecolums // ...c1.Institution, c2.Institution
FROM produktion p, requests r, contacts c1, contacts c2
WHERE p.P_ID = r.P_ID // pick some specific productionIDs
AND c1.K_ID =* r.fromK_ID
AND c2.K_ID =* r.toK_ID

If that syntax is accepted but the result is wrong, use * = rather than = *.

10

can you share some of your dirty drink? just reading this got me thirsty :slight_smile:

11

Nope. That's an "inner join", me and my drink.

12

okay guys, did some homework. Seems, that my database doesn't support full outer join. Scott was about right at first sight. I did a workaround creatimg
A left join B
Union
B left join A

Thanks a Million for all the guidance and patience.

Cheers, lars

13

Np, just glad you got a resolution.