LEFT JOIN for noobs |
[eluser]slowgary[/eluser]
Hi guys, I have a query that left joins a few tables on the primary id, but when I get the results back the id field is duplicated (because it's in each table). So my results look like this: Code: prod_id | prod_price | prod_sku | prod_id | prod_image | prod_id | prod_stock This works fine when all fields have data in them, but when the last table returns NULL as the prod_id, my code gets that one, which breaks everything. Is there a way to only get back one prod_id field? Preferably from the first table. Thanks.
[eluser]sl3dg3hamm3r[/eluser]
Why don't you define the fields you want in a select-statement?
[eluser]slowgary[/eluser]
Is that the only way? I was going to try that but the only fields I DON'T want are the multiple IDs. That means I'd have to specify every field and there are a bunch of them. Also, if I add new fields to the table I'd also have to change the code. Is there a way to invert a selection, or just say "! table2.prod_id"?
[eluser]slowgary[/eluser]
It was actually less fields than I thought because the first table contained most of them, and I only needed 1 field from each of the other tables, so I have something like: Code: ->select('table1.*, table2.field, table3.field') That works like a charm. Thanks sl3dg3. Would be nice to have a SELECT INVERT though for other cases.
[eluser]TheFuzzy0ne[/eluser]
You can also use an alias. You can also add an alias: Code: ->select('table1.prod_id prod_id')
[eluser]Zorancho[/eluser]
If for example you have 3 tables that have prod_id field like this: table1: prod_id, prod_image, prod_name; table2: prod_id, prod_image, prod_name; table3: prod_id, prod_image, prod_name; And you want to select all, but only the prod_id from table1, you do it this way: Code: $sql = "SELECT tone.*, |
Welcome Guest, Not a member yet? Register Sign In |