SQL Datenbank Abfrage - Lieferadresse

[ianmoone]

Hallo, ich rufe mit folgender Abfrage die Daten zu meinen Bestellungen ab um sie in einer Liste weiter zu verwenden. Ich würde gerne die Lieferadresse mit Abrufen, finde aber leider nicht wo diese gespeichert ist..

 

SELECT d.id_order, os.name AS Status, d.product_name AS Produkt, ROUND(d.product_price,2) AS Preis, d.product_quantity AS Menge, o.payment AS Bezahlung, o.date_upd AS Datum, CONCAT_WS(' ', a.firstname, a.lastname) AS Kunde, a.address1 AS Strasse, a.postcode AS PLZ, a.city AS Ort, cl.name AS Lande, gl.name AS Kundengruppe
    FROM d72nF_order_detail d
    LEFT JOIN d72nF_orders o ON (d.id_order = o.id_order)
    LEFT JOIN d72nF_customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN d72nF_address AS a ON (o.id_customer = a.id_customer)
    LEFT JOIN d72nF_country_lang AS cl ON (a.id_country = cl.id_country )
    LEFT JOIN d72nF_group_lang gl ON (g.id_default_group = gl.id_group)
    LEFT JOIN d72nF_order_state_lang os ON (o.current_state = os.id_order_state)

GROUP by  CONCAT_WS(' ', d.product_name, g.lastname, g.firstname,d.id_order ) 
ORDER BY d.id_order DESC

 

[ps8modules]

WHERE a.id_address = o.id_address_delivery

SELECT d.id_order, os.name AS Status, d.product_name AS Produkt, ROUND(d.product_price,2) AS Preis, d.product_quantity AS Menge, o.payment AS Bezahlung, o.date_upd AS Datum, CONCAT_WS(' ', a.firstname, a.lastname) AS Kunde, a.address1 AS Strasse, a.postcode AS PLZ, a.city AS Ort, cl.name AS Lande, gl.name AS Kundengruppe
    FROM d72nF_order_detail d
    LEFT JOIN d72nF_orders o ON (d.id_order = o.id_order)
    LEFT JOIN d72nF_customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN d72nF_address AS a ON (o.id_customer = a.id_customer)
    LEFT JOIN d72nF_country_lang AS cl ON (a.id_country = cl.id_country )
    LEFT JOIN d72nF_group_lang gl ON (g.id_default_group = gl.id_group)
    LEFT JOIN d72nF_order_state_lang os ON (o.current_state = os.id_order_state)
WHERE a.id_address = o.id_address_delivery
GROUP by  CONCAT_WS(' ', d.product_name, g.lastname, g.firstname,d.id_order ) 
ORDER BY d.id_order DESC;

[ianmoone]

Funktioniert perfekt. Danke. Und wenn es ich es auf 2023 eingrenzen möchte? ich weis das sind eigentlich sql basics. Muss ich mich umbedingt mehr beschäftigen damit!

[ps8modules]

5 minutes ago, ianmoone said:

Funktioniert perfekt. Danke. Und wenn es ich es auf 2023 eingrenzen möchte? ich weis das sind eigentlich sql basics. Muss ich mich umbedingt mehr beschäftigen damit!

WHERE a.id_address = o.id_address_delivery AND o.date_upd LIKE '2023%'

Edited November 14, 2023 by ps8moduly.cz (see edit history)

[Nickz]

1 hour ago, ps8moduly.cz said:

WHERE a.id_address = o.id_address_delivery AND o.date_upd LIKE '2023%'

'2023%' or  rather '%2023%'  ????

[ianmoone]

1 minute ago, Nickz said:

'2023%' or  rather '%2023%'  ????

'2023%'  works...

[ps8modules]

7 minutes ago, ianmoone said:

'2023%'  works...

Ich habe gerne geholfen. Klicken Sie zum Liken auf das graue Herz unter meinen Beiträgen. 😏

[ps8modules]

12 minutes ago, Nickz said:

'2023%' or  rather '%2023%'  ????

„%2023%“ wird nicht benötigt, da das % davor unnötig ist. Vor 2023 gibt es nichts 😉