1

I am trying to write a SQL query in Delphi, but yet without luck.

I have two tables, the first one holds personal data, the second one holds information about purchased goods.

My query works fine when I connect the two tables with WHERE clause (my key is person_id).

But I would like to add records to my query that have no personal data, just someone purchased the goods, they don't display.

So in those records where there isn't any personal information I need to have empty strings or null values.

Is it possible to do in one SQL query?

Edit: Here is the working version, but this doesn't contain the second table's values that are not related to the first table.

Query1.SQL.Add ('SELECT idcard, vnev, knev, kapcs, ');
Query1.SQL.Add ('bsz, bt, kidate, ervvege, alkalmak FROM "'+adathely+'", "'+berlethely+'" ');
Query1.SQL.Add ('WHERE ("'+adathely+'".idcard = "'+berlethely+'".idcard) ');
//from here only filtering occurs
Query1.SQL.Add ('AND kidate >= "'+IntToStr(DateToInt(filterdate1.Text))+'" ');
Query1.SQL.Add ('AND ervvege <= "'+IntToStr(DateToInt(filterdate2.Text))+'" ');
Query1.SQL.Add ('AND CAST(bsz AS CHAR(6)) LIKE '''+filterbsz.Text+'%''  ');
Query1.SQL.Add ('AND ((LOWER(vnev) LIKE ''%'+filtername.Text+'%'') OR (LOWER(knev) LIKE ''%'+filtername.Text+'%''))  ');
Query1.SQL.ADD ('ORDER BY vnev ASC ');

A little explanation: table1 is "adathely" table2 is "berlethely" Both tables contain the field 'idcard', but in table2 there are records that do not have value in this field, but i also would like to display them in my stringgrid.

Raidri
  • 17,258
  • 9
  • 62
  • 65
Yohnsee
  • 33
  • 1
  • 4

1 Answers1

1

I think you wrote a query like this:

SELECT *
FROM Table1, Table2
WHERE Table1.person_id = Table2.person_id

and if person_id is present in Table1 but not in Table2, no record will appear.

Try a LEFT JOIN instead:

SELECT *
FROM Table1 LEFT JOIN Table2 on Table1.person_id = Table2.person_id

This will show every record in Table1 even if it is not correlated to a record in Table2.

EDIT: if a left join is not enough, you probably need a FULL OUTER JOIN. If your DBMS does not support FULL OUTER JOIN, you could simulate it using this:

SELECT *
FROM Table1 LEFT JOIN Table2 on Table1.person_id = Table2.person_id
UNION SELECT *
FROM Table1 RIGHT JOIN Table2 on Table1.person_id = Table2.person_id

and since there are a lot of conditions in the where clause, i'd suggest you to write your query like this:

SELECT *
FROM (the union query above) tables
WHERE ...all the conditions,
  except table1.idcard = table2.idcard that's already included in the join...

just to make things more readable.

fthiella
  • 48,073
  • 15
  • 90
  • 106
  • Thank you, but I think Right outer join is what I will need (as @marc_s said). The only problem is now, that no fields from table2 are included in my query. – Yohnsee Nov 15 '12 at 14:44
  • thats because you need a left outer join! – StevieG Nov 15 '12 at 15:07
  • you probably need a FULL OUTER JOIN that shows every row from table1 and every row from table 2, even if they are not correlated – fthiella Nov 15 '12 at 16:43
  • @StevieG : Thank you, it works perfect with a simple right join. All I need to figure out is how can I make the query put the null values on the end of the list. – Yohnsee Nov 16 '12 at 07:48
  • To order the results, you have to add a `ORDER BY field1,field2,etc.` clause at the end of your query. But if `field` contains null values, and you want to put that values at the end of the list you could use this order by clause: `ORDER BY field1 is null, field2 is null, etc.` (this works in MySql but other DBMS may have a different syntax) – fthiella Nov 16 '12 at 09:04
  • if you want to order by field1 but you also want to put null values at the end, try something like this: `ORDER By field1 is null, field1, etc.` – fthiella Nov 16 '12 at 09:05