Search the Community

I have two tables with primary key and foreign key (ItemId) Table 1 (Items) ItemId ItemName OPStock OPStockValue 1 Pen 100 1000 2 Pencil 50 5000 Table 2 (Inventory) TransactionType VchNo ItemID ItemName Qty TaxableAmt EntryDate Sale 2 1 KFL UREA NEEM COTED 70 16940 16-04-2020 Sale 1 5 IPL MOP 60 49200 16-04-2020 Sale 3 2 IPL DAP (IMPORTED) 60 58800 16-04-2020 Sale 4 2 IPL DAP (IMPORTED) 10 9800 16-04-2020 Sale 4 5 IPL MOP 50 41000 16-04-2020 Sale 5 2 IPL DAP (IMPORTED) 10 9800 23-04-2020 Purchase 1 67 PADDY SEEDS DAYAL GOLD 30 201000 23-04-2020 Purchase 1 97 PADDY SEEDS DAYAL GOLD 15 100500 23-04-2020 Sale 8 2 IPL DAP (IMPORTED) 10 9800 24-04-2020 Purchase 2 1 KFL UREA NEEM COTED 1600 381008.16 24-04-2020 Purchase 3 7 CASTER OIL CAKE ORGANIC 500 261250 24-04-2020 Purchase 4 38 PADDY SEEDS MANSURI 175 682500 02-05-2020 Sale 19 39 PADDY SEEDS MTU-1001 5 20750 02-05-2020 Sale 19 40 PADDY SEEDS MTU7029 1.25 4812.5 03-05-2020 Now When I Pass @FromDate and @Todate I want to output like below: EntryDate ItemName OpQty PurchQty SaleQty ClosingBal database link What I have tried: I am trying to solve using Left Join But the result is not correct SELECT I.ItemID, I.ItemName, I.OPStock AS OpStock, I.OPStockValue AS OpValue, SUM(SP.Qty) AS InQty, SUM(SP.TaxableAmt) AS InValue, SUM(SS.Qty) AS OutQty, SUM(SS.TaxableAmt) AS OutValue FROM items AS I LEFT JOIN inventory AS SP ON SP.ItemID = I.ItemID LEFT JOIN inventory AS SS ON I.ItemID = SS.ItemID WHERE SP.TransactionType = 'Purchase' AND SS.TransactionType = 'Sale' GROUP BY I.ItemID ORDER BY I.ItemName ASC

Hello, my question is regarding the code below. I usually see double left joins which refer to the table1 for both LEFT JOINS (FROM cities/cities as tabl1), this one is referring to countries.code as table1 in the second LEFT JOIN which confuses me somewhat. Is the first or second LEFT JOIN excecuted first? Basically I'm asking which has priority and which table gets LEFT JOINed first. SELECT cities.name AS city, urbanarea_pop, countries.name AS country, indep_year, languages.name AS language, percent FROM cities LEFT JOIN countries ON cities.country_code = countries.code LEFT JOIN languages ON countries.code = languages.code ORDER BY city, language; SELECT cities.name AS city, urbanarea_pop, countries.name AS country, indep_year, languages.name AS language, percent from languages right join countries on languages.code = countries.code right join cities on countries.code = cities.country_code order by city, language;

I would like to connect to a mysql database that is a localhost connection and not on a internet service provider. How do i connect? Do you use "localhost or your ip"? I tried it and it does not work. Also, how do i open a port for access? Is the port connections blocking me from succeeding regarding this?

I want to know how I would go about making a script where a user would type in a message and post that said message. Is sql required?

Each SQL function and capability should indicate if it is ANSI standard compliant and the version in which it was first found and the earliest version of the database which supported it. SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2006 SQL:2008 SQL:2011 SQL:2016 SQL:2019

I am building a website and want to test it for SQL injection vulnerability, when I run the injection, where would I view the results?

Hi all, Newbie to the forum and the world of SQL, hence apologies for my naivety. From the attached table, I'm trying to create a range (as an output) and create a summary as shown in the attached in yellow. The reason being, the size of raw data (+2m rows). Trying to do this ins Microsoft SQL Management Studio. Any help would be greatly appreciated. Thank you. SQL.xlsx

I am stuck with a problem. Have a shopping cart, once the client selects the products, from a SQL table (1), they are taken up by an array variable A session array variable delivers values using foreach loop. The php shows all the values sequentially But the SQL table (2) gets updated with only the last value. Understand that this will happen due to looping. So how to go about resolving this

Just to preface: Thank you W3Schools for these tutorials!!! Now on to the possible issue: In your SQL Tutorial I am unable to view the "SQL Intro" page. When I click on it I keep getting "502 - Bad Gateway." I've tried navigating to it via the "Next" button at the bottom of the "SQL HOME" page but I get the same message. The URL it is trying to access is "https://www.w3schools.com/sql/sql_intro.asp" I was unsure if this was something others were seeing or just something on my end.

Hi, I am not sure how to write a query for the below case, Pls help me out. ID description values M1 ab1 23 M1 ab2 54 M1 ab3 23 M2 ab1 67 M2 ab2 56 M2 ab3 91 M3 ab1 41 M3 ab2 53 M3 ab3 27 M3 ab4 41 Conditions: I need to pick the row when values are same for different description under similar ID, Example: Under ID (M1) , I have the description (ab1 and ab3) have same values (23), Similarly ID (M3) have same values 41 for descripttion ab1 and ab4. hence the result must be only Red texted in the table. Thanks In advance.

I am joining 2 tables using the query below. Its many to many relationship. The join works fine but creates multiple rows for each item which is expected but the Amount/value column is also duplicated. How to avoid this? Thanks As you can see the Current output the value (50000 appears 3 times instead of 1 and 27000 appears 3 times instead of 1) SELECT * FROM T1 INNER JOIN T2 ON T1.Projectex = T2.WBS_Parent Table 1: +-----------+------------+-----------+----------+--------+----------+-------+ | Projectex | CAPEX_OPEX | costelmnt | sap_vers | Period | FISCYEAR | VALUE | +-----------+------------+-----------+----------+--------+----------+-------+ | 0-01081 | CAPEX | 3416 | 61 | 3 | 2020 | 50000 | | 0-01081 | OPEX | 7077 | 30 | 5 | 2020 | 27000 | +-----------+------------+-----------+----------+--------+----------+-------+ Table2: +------------+-----------------+---------+---------+ | WBS_PARENT | FINANCILAL_YEAR | MEASURE | AMOUNT | +------------+-----------------+---------+---------+ | 0-01081 | 2020 | CPX | 2000000 | | 0-01081 | 2020 | OPX | 50000 | | 0-01081 | 2020 | OPX | 1000000 | +------------+-----------------+---------+---------+ CURRENT OUTPUT: Projectex| CAPEX_OPEX| costelmnt| sap_vers| Period| FISCYEAR| VALUE| 0 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 1 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 2 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 3 0-01081| OPEX| 7077| 30| 5| 2020| 27000| 4 0-01081| OPEX| 7077| 30| 5| 2020| 27000| 5 0-01081| OPEX| 7077| 30| 5| 2020| 27000| WBS_PARENT FINANCILAL_YEAR MEASURE AMOUNT 0 0-01081| 2020| CPX| 2000000 1 0-01081| 2020| OPX| 50000 2 0-01081| 2020| OPX| 1000000 3 0-01081| 2020| CPX| 2000000 4 0-01081| 2020| OPX| 50000 5 0-01081| 2020| OPX| 1000000 Expected Output: Projectex| CAPEX_OPEX| costelmnt| sap_vers| Period| FISCYEAR| VALUE| 0 0-01081| CAPEX| 3416| 61| 3| 2020| 50000| 1 0-01081| CAPEX| 3416| 61| 3| 2020| 2 0-01081| CAPEX| 3416| 61| 3| 2020| 3 0-01081| OPEX| 7077| 30| 5| 2020| 27000| 4 0-01081| OPEX| 7077| 30| 5| 2020| 5 0-01081| OPEX| 7077| 30| 5| 2020| WBS_PARENT FINANCILAL_YEAR MEASURE AMOUNT 0 0-01081| 2020| CPX| 2000000 1 0-01081| 2020| OPX| 50000 2 0-01081| 2020| OPX| 1000000 3 0-01081| 2020| CPX| 4 0-01081| 2020| OPX| 5 0-01081| 2020| OPX|

I have a problem with an XML file that has the following structure: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <DATAPACKET Version="2.0"> <METADATA><FIELDS> <FIELD attrname="Id" fieldtype="i4" readonly="true" SUBTYPE="Autoinc"/> <FIELD attrname="Blocco" fieldtype="string" WIDTH="5"/> <FIELD attrname="Domanda" fieldtype="string" WIDTH="2"/> <FIELD attrname="Risposta" fieldtype="boolean"/> <FIELD attrname="Capitolo" fieldtype="string" WIDTH="2"/> <FIELD attrname="Indice" fieldtype="string" WIDTH="3"/> <FIELD attrname="Argomento" fieldtype="string" WIDTH="1"/> <FIELD attrname="SubArgomento" fieldtype="string" WIDTH="2"/> <FIELD attrname="Figura" fieldtype="string" WIDTH="4"/> <FIELD attrname="FiguraBlk" fieldtype="string" WIDTH="4"/> <FIELD attrname="Difficolta" fieldtype="string" WIDTH="2"/> <FIELD attrname="Testo" fieldtype="string" WIDTH="320"/> <FIELD attrname="Lingua1" fieldtype="string" WIDTH="320"/> <FIELD attrname="Lingua2" fieldtype="string" WIDTH="320"/> <FIELD attrname="Lingua3" fieldtype="string" WIDTH="320"/> <FIELD attrname="Commento" fieldtype="string" WIDTH="256"/> <FIELD attrname="Aiuto" fieldtype="string" WIDTH="128"/> <FIELD attrname="Foto1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto3" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto4" fieldtype="string" WIDTH="5"/> <FIELD attrname="Foto5" fieldtype="string" WIDTH="5"/> <FIELD attrname="Video1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Edl1" fieldtype="string" WIDTH="15"/> <FIELD attrname="Video2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Edl2" fieldtype="string" WIDTH="15"/> <FIELD attrname="Video3" fieldtype="string" WIDTH="5"/> <FIELD attrname="Edl3" fieldtype="string" WIDTH="15"/> <FIELD attrname="Audio1" fieldtype="string" WIDTH="12"/> <FIELD attrname="Audio2" fieldtype="string" WIDTH="12"/> <FIELD attrname="Audio3" fieldtype="string" WIDTH="12"/> <FIELD attrname="Html1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Html2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Html3" fieldtype="string" WIDTH="5"/><FIELD attrname="Libro1" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro1PosY" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro2" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro2PosY" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro3" fieldtype="string" WIDTH="5"/> <FIELD attrname="Libro3PosY" fieldtype="string" WIDTH="5"/> <FIELD attrname="Info1" fieldtype="string" WIDTH="120"/> <FIELD attrname="Info2" fieldtype="string" WIDTH="120"/> <FIELD attrname="Gruppo1" fieldtype="string" WIDTH="3"/> <FIELD attrname="Gruppo2" fieldtype="string" WIDTH="3"/> <FIELD attrname="Gruppo3" fieldtype="string" WIDTH="3"/> </FIELDS><PARAMS AUTOINCVALUE="7166"/></METADATA> <ROWDATA> <ROW Id="2" Blocco="11023" Domanda="02" Risposta="TRUE" Capitolo="01" Indice="A01" Argomento="A" SubArgomento="1" Figura="" FiguraBlk="" Difficolta="6" Testo="I ciclomotori possono avere due o tre ruote" Lingua1="Les motocycles légers peuvent avoir deux ou trois roues" Lingua2="Kleinkrafträder können zwei oder drei Räder haben" Lingua3="I ciclomotori possono avere due o tre ruote" Commento="infatti i CICLOMOTORI possono avere DUE, TRE e anche QUATTRO RUOTE, cilindrata fino a 50 cm³ e velocità fino a 45 km/h." Aiuto="Classificazione dei veicoli." Foto1="3113" Foto2="" Foto3="" Foto4="" Foto5="" Video1="" Video2="" Video3="" Audio1="04023_40231" Audio2="" Audio3="" Html1="" Html2="" Html3="" Libro1="1" Libro2="1" Libro3="" Info1="11023" Info2="Ciclomotori"/> <ROW Id="3" Blocco="11023" Domanda="03" Risposta="TRUE" Capitolo="01" Indice="A01" Argomento="A" SubArgomento="1" Figura="" FiguraBlk="" Difficolta="5" Testo="Non tutti i veicoli a motore a due ruote vengono classificati ciclomotori" Lingua1="Pas tous les véhicules à moteur à deux roues peuvent être classifiés des motocycles légers" Lingua2="Nicht alle zweirädrigen Kraftfahrzeuge werden als Kleinkrafträder eingestuft" Lingua3="Non tutti i veicoli a motore a due ruote vengono classificati ciclomotori" Commento="infatti vengono CLASSIFICATI CICLOMOTORI solo i veicoli a DUE RUOTE con CILINDRATA NON SUPERIORE a 50 cm³ e VELOCITÀ NON SUPERIORE a 45 km/h." Aiuto="Classificazione dei veicoli." Foto1="1238" Foto2="" Foto3="" Foto4="" Foto5="" Video1="" Video2="" Video3="" Audio1="04023_40232" Audio2="" Audio3="" Html1="" Html2="" Html3="" Libro1="1" Libro2="1" Libro3="" Info1="11023" Info2="Ciclomotori"/> it continues with this structure but it is very long. How can I do from my SQL DB to extract a data ("Libro3") to insert it inside every occurrence of ''Libro3' of XML file? In my sql to recognize the line to be modified I have Id,Blocco, Libro3 obviously, but i don t know how i can modify the file. to recognize the line to be modified on the sql I have line, id and block

ID Name Orderdate Catalog Price 7b 34-10 NULL 3000 7b 34-10 NULL 3000 7b 34-10 NULL 2000 7b 35-12 PL-17 3000 8b 35-11 PL-18 4000 8b 34-13 PL-18 4000 8b 34-14 PL-18 4000 8b 34-15 PL-18 4000 9b 35-12 PL-19 5000 9b 35-11 PL-19 5000 9b 34-18 PL-19 5000 9b 34-19 PL-19 5000 9b 34-20 PL-19 5000 I want a List of the products where Id starts with 7 where Name starts with 34 where Orderdate = null whit the highest catalog price Output should be this ID Name Orderdate Catalog Price 7b 34-10 NULL 3000 7b 34-10 NULL 3000

Hello, I have installed different versions of SQL Server in my laptop (Asus X44H), but here is the error -> (provider: Named pipes provider, error:40- could not open a connection to SQL Server)(Microsoft SQL Server, Error:2) SQL Server instance MSSQLSERVER could not be installed, I don't know why! (I checked that in services.msc, there is no MSSQLSERVER), I have also changed my windows-10 but the issue has not been resolved yet. Any Solutions? Thanks.

Hi . I'm having trouble with the AUTO INCREMENT statement in SQL. I've built the table : CREATE TABLE tenants( apartment_number_first_floor INT AUTO_INCREMENT, family_name VARCHAR (12) DEFAULT NULL, sur_name VARCHAR (8) DEFAULT NULL, telephone_number INT(3) NOT NULL, PRIMARY KEY (apartment_number_first_floor) ); when inserting a row: INSERT INTO tenants VALUES(' ',' '); The IDE doesn't accept the column count: ER_WRONG_VALUE_COUNT_ON_ROW: Column count doesn't match value count at row 1 So i tried filling the rows just to check if the IDE will run the statement INSERT INTO tenants VALUES(103,'שקלובין','מרתה',203); but the IDE refuses to increment Any ideas? Thanks

Hi, How can I show string results as characters in sql plus? select* from example a where a.insert_time>='12-dec19' and a.status=1 as 'Cars I want that if 'status' field status result would show '1' the the output would show 'Cars' and if the result would show '2' the the output would show 'Bikes' instead of strings. Best regards

Hi, How can I show string results as characters in sql plus? select* from example a where a.insert_time>='12-dec19' and a.status=1 as 'Cars I want that if 'status' field status result would show '1' the the output would show 'Cars' and if the result would show '2' the the output would show 'Bikes' instead of strings. Best regards

Hi I have a query that returns the count of entries based on CURDATE(), it works for any dat in this month (April) but does not work for next month (May). I think it is something to do with CURDATE()+3 returns 2018-04-31 which should be 2018-05-01 This Works as Date is in April SELECT COUNT(Machine) FROM production WHERE Status = 'Confirmed' AND DATE(FROM_UNIXTIME(production.Required)) = CURDATE() This does not work as in May SELECT COUNT(Machine) FROM production WHERE Status = 'Confirmed' AND DATE(FROM_UNIXTIME(production.Required)) = CURDATE()+3

There is a table, named student_mark and I have to find which 2 students are having min marks.I also tried group by but it is not working.

I want to develop a library system in which fine will be calculated as follows- the code should be in php and sql database. I want to retrieve date from database and subtract it from current date. The difference will be then multiplied with 5 to know the fine. How can I do this? How can i retrieve date from database and subtract it from current date? returndate is column name from where i want to retrieve date for id=1 and issret is the table name. <form id="form1" name="form1" method="post" action=""><label for="txtDueDate"></label><input type="text" name="DueDate" id="txtDueDate" /><input type="submit" name="submit" id="submit" value="Submit" /></form><?phpif(isset($_POST['submit'])){$result=mysql_query("select returndate from issret where id=1"); if(mysql_num_rows($result)>0){$x=date("Y/m/d");$diff=date_diff($result,$x);echo $diff->format("%R%a days");}}?>

Hi, I'm using the SQL TryIt Editor but the Truncate Table command seems to fail when running. It states there is a syntax error but i cannot see any issue. Is anyone else able to truncate one of the existing tables? Thanks,

Hello everyone, i must do a query that finds all words that finish whit a,e,i,o or u. i' ve used this query but my output in empty . select City from Station where City like '%[aeiou]'; i' ve used the wildcards wrongly?

Hello All, I am trying to get the latest record available for a field, I want to learn how to write/pull what I know as of record status "1" from my results since let's say; an activity was updated several times but I just need the latest update base on the date, I tried using distinct and it worked: each row is different but I am still receiving all the updates of each activity. I tried to use (Group by) base in the activity Id and I am receiving this error message "Column 'activity_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I am using the Microsoft SQL server management studio.

Hi, I want to be able to filter by date looking back 6 months. I then want to include data from 12 months, 18 months, 24 months etc How would i do this without having to repeat myself? Currently i am using the below and then repeating it e.g between dateadd(dd,-189,getdate()) and dateadd(dd,-183,getdate()) between dateadd(dd,-373,getdate()) and dateadd(dd,-366,getdate()) Thanks in Advance,